package cn.lili.common.utils;

import cn.lili.common.exception.ServiceException;
import org.springframework.util.CollectionUtils;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * sql转换  增删改 --> 查询
 */
public class SQLUtil {

    /**
     * 转换方法
     * @param sql 增删除sql
     * @return 查询sql
     */
    public static String sqlConversion(String sql) {
        int sqlType = getSqlType(sql);
        String table = getTable(sql);
        String resultSql = "";
        if (sqlType == 0 ||  StringUtils.isEmpty(table)) {
            System.out.println("sqlType = " + sqlType);
            System.out.println("table = " + table);
            System.out.println("sql = " + sql);
            throw new ServiceException("sql转换异常");
        }
        try {
            if (sqlType == 1) {
                resultSql = insertSql(sql);
            }
            //删除转查询
            if (sqlType == 2) {
                resultSql = sql.toLowerCase().replace("delete","select *");
            }
            //修改转查询
            if (sqlType == 3) {
                int whereIndex = sql.toLowerCase().indexOf(" where ");
                String where = sql.substring(whereIndex);
                resultSql = "select * from " + table + where;
            }
        } catch (Exception e) {
            System.out.println("e.getMessage() = " + e.getMessage());
            e.printStackTrace();
        }
        return resultSql;
    }

    public static String getTable(String sql) {
        int isTable = 0;
        if (sql.toLowerCase().contains("li_goods")) {
            isTable = 1;
        }
        if (sql.toLowerCase().contains("li_goods_sku")) {
            isTable = 2;
        }
        return isTable == 1? "li_goods" : "li_goods_sku";
    }

    /**
     * 获取sql类型
     *
     * @param sql sql
     * @return  1新增 ， 2删除，  3修改
     */
    public static int getSqlType(String sql) {
        int isType = 0;
        String sqlType = sql.substring(0,6);
        if (sqlType.equalsIgnoreCase("insert")){
            isType = 1;
        }
        if (sqlType.equalsIgnoreCase("delete")){
            isType = 2;
        }
        if (sqlType.equalsIgnoreCase("update")){
            isType = 3;
        }
        return isType;
    }
    /**
     * 新增sql  --> 查询sql
     * @param sql 新增sql
     *
     * @return 查询sql
     */
    public static String insertSql(String sql) {
        HashMap<String, Object> insertMap = getInsertMap(sql);
        return splitSql(insertMap);
    }
    /**
     * 拼接sql语句
     */
    public static String splitSql(HashMap<String, Object> map) {
        String table = (String) map.get("table");
        map.remove("table");
        StringBuilder builder = new StringBuilder();
        for (String key : map.keySet())  {
            if (!key.equals("id")) {
                continue;
            }
            builder.append(key).append(" = ").append("'").append(map.get(key)).append("'").append(" and ");
        }
        // 去除最后的”and“
        builder.delete(builder.length() - 4, builder.length());
        return "select * from " + table + " where " + builder;
    }


    /**
     * 获取insert语句中的value值
     */
    public static List<String> getValues(String str) {
        //获得字段值
        String string = "";
        String regex2 = "(values ?|VALUES ?)(\\()(.+)(\\))";
        Pattern compile2 = Pattern.compile(regex2);
        Matcher matcher2 = compile2.matcher(str);
        while (matcher2.find()) {
            string = matcher2.group(3);
        }
        List<String> list = new ArrayList<>();
        StringBuilder concatStr = new StringBuilder();
        for (int i = 0; i < string.length(); i++) {
            char c = string.charAt(i);
            // 针对括号里的内容进行获取
            if(string.charAt(i) == '(') {
                int num = 0;
                for (int j = i; j < string.length(); j++) {
                    if (string.charAt(j) == '(') {
                        num++;
                    }
                    if (string.charAt(j) == ')') {
                        concatStr.append(string.charAt(j));
                        num --;
                        if (num == 0) {
                            i = j+1;
                            break;
                        }
                        continue;
                    }
                    concatStr.append(string.charAt(j));
                }
            }
            // 针对单引号里的内容进行获取
            if (c == '\'' || c == '\"') {
                if (string.charAt(i+1) == '\'' || string.charAt(i+1) == '\"') {
                    i++;
                    continue;
                }
                for (int j = i+1; j < string.length(); j++) {
                    char charAt = string.charAt(j);
                    if (charAt == '\'' || charAt == '\"') {
                        i = j+1;
                        break;
                    }
                    concatStr.append(charAt);
                }
            }
            if(i >= string.length()){
                break;
            }
            if (i == string.length()-1) {
                if (string.charAt(i) == '\'' || c == '\"') {
                    break;
                }
                else {
                    concatStr.append(c);
                    break;
                }
            }
            if (string.charAt(i) == ',') {
                list.add(concatStr.toString());
                concatStr = new StringBuilder();
                continue;
            }
            if (c == ' '){
                continue;
            }
            concatStr.append(string.charAt(i));
        }
        list.add(concatStr.toString());
        return list;
    }

    /**
     * 得到一个key为字段value为参数值的map
     */
    public static HashMap<String, Object> getInsertMap(String sql) {
        //获得字段
        List<String> key = getKeys(sql);

        //获得字段值
        List<String> value = getValues(sql);

        //组合成一个map
        HashMap<String, Object> map = new HashMap<>();
        try {
            for (int i = 0; i < value.size(); i++) {
                if (i == 0) {
                    // 将表名放入map中
                    map.put("table", key.get(i));
                    continue;
                }
                map.put(key.get(i), value.get(i-1));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        // 去除公式生成的time或其它随机生成的id等信息
        map.remove("param_id");
        map.remove("create_time");
        map.remove("modify_time");
        ArrayList<String> delete = new ArrayList<>();
        // 去除字段值为NULL和null还有为空的字段
        for (String strKey : map.keySet()) {
            if (map.get(strKey).equals("NULL") || map.get(strKey).equals("") || map.get(strKey).equals("null")) {
                delete.add(strKey);
            }
        }
        //删除值为空或为Null或为随机数的字段和值
        delete.forEach(map::remove);

        return map;
    }



    /**
     * 获取insert语句中的key值
     */
    public static List<String> getKeys(String str) {
        ArrayList<String> key = new ArrayList<>();
        String regex = "`(\\w+)`(?!\\.)";
        Pattern compile = Pattern.compile(regex);
        Matcher matcher = compile.matcher(str);
        while (matcher.find()) {
            key.add(matcher.group(1));  //group(1):表示第一个（）里的内容
        }

        // 如果表名和字段名没有反单引号，采取下列措施
        if (CollectionUtils.isEmpty(key)) {
            Pattern compileTable = Pattern.compile("(to|TO) (\\w+)");
            Matcher matcherTable = compileTable.matcher(str);
            if (matcherTable.find()) {
                key.add(matcherTable.group(2));
            }
            Pattern compile1 = Pattern.compile("(\\()(.+)(\\))( ?)([Vv])");
            Matcher matcher1 = compile1.matcher(str);
            while (matcher1.find()) {
                String s = matcher1.group(2);
                key.addAll(Arrays.asList(s.replace(" ", "").split(",")));
            }
        }

        return key;
    }




}
